"""
Case Type   : Compatibility
Case Name   : stmt_name参数类型进行验证;部分测试点合理报错
Create At   : 2024/10/14
Owner       : liu-tong-8848
Description :
              1.stmt_name参数是表名
              2.stmt_name参数是视图名
              3.stmt_name参数是列名
              4.stmt_name参数是索引名
              5.stmt_name参数是数据库名
              6.stmt_name参数是自定义函数名
              7.stmt_name参数是存储过程名
              8.stmt_name参数是角色名
              9.stmt_name参数是用户组名
              10.stmt_name参数是模式名
              11.stmt_name参数是用户名
              12.清理环境
Expect      :
              1.成功
              2.成功
              3.成功
              4.成功
              5.成功
              6.成功
              7.成功
              8.成功
              9.成功
              10.成功
              11.成功
              12.清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.db_user_node = Node(node='PrimaryDbBmode')

    def test_add_dimension(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        step1 = '--step1:stmt_name参数是表名;expect:成功'
        self.log.info(step1)
        sql = "drop table if exists t_prepare_002; create table t_prepare_002(c int); " \
              + "prepare t_prepare_002 from 'select 1'; execute t_prepare_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step1)

        step2 = '--step2:stmt_name参数是视图名;expect:成功'
        self.log.info(step2)
        sql = "drop view if exists test_view_002; create view test_view_002 as select * from t_prepare_002; " \
              + "prepare test_view_002 from 'select 1'; execute test_view_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step2)

        step3 = '--step3:stmt_name参数是列名;expect:成功'
        self.log.info(step3)
        sql = " prepare c from 'select 1'; execute c;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step3)

        step4 = '--step4:stmt_name参数是索引名;expect:成功'
        self.log.info(step4)
        sql = "create index test_index_002 on t_prepare_002(c); prepare test_index_002 from 'select 1'; " \
              + "execute test_index_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step4)

        step5 = '--step5:stmt_name参数是数据库名;expect:成功'
        self.log.info(step5)
        sql = "drop database if exists test_database_002; create database test_database_002; " \
              + "prepare test_database_002 from 'select 1'; execute test_database_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step5)

        step6 = '--step6:stmt_name参数是自定义函数名;expect:成功'
        self.log.info(step6)
        sql = "create or replace function f_prepare_002(c1 bigint) returns bigint as \$\$ begin select 1;" \
              + "raise info '测试stmt_name参数是自定义函数名'; end; \$\$language plpgsql;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step6)
        sql = "prepare f_prepare_002 from 'select 1'; execute f_prepare_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step6)

        step7 = '--step7:stmt_name参数是存储过程名;expect:成功'
        self.log.info(step7)
        sql = "CREATE OR REPLACE PROCEDURE p_prepare_002() AS str varchar; BEGIN select 1; " \
              + "raise info '测试stmt_name参数是存储过程名'; END;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step7)
        sql = "prepare p_prepare_002 from 'select 1'; execute p_prepare_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step7)

        step8 = '--step8:stmt_name参数是角色名;expect:成功'
        self.log.info(step8)
        sql = "drop role if exists test_role_002; create role test_role_002 with password 'gauss@123' " \
              + "valid until '2001-12-12'; prepare test_role_002 from 'select 1'; execute test_role_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step8)

        step9 = '--step9:tmt_name参数是用户组名;expect:成功'
        self.log.info(step9)
        sql = "drop group if exists test_group_002; create group test_group_002 with password 'gauss@123'; " \
              + "prepare test_group_002 from 'select 1'; execute test_group_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step9)

        step10 = '--step10:stmt_name参数作为模式名;expect:成功'
        self.log.info(step10)
        sql = "drop schema if exists test_shema_002; create schema test_shema_002; " \
              + "prepare test_shema_002 from 'select 1'; execute test_shema_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step10)

        step11 = '--step11:stmt_name参数作为用户名;expect:成功'
        self.log.info(step11)
        sql = "drop user if exists u_prepare_002; CREATE USER u_prepare_002 PASSWORD 'Bigdata@123'; " \
              + "prepare u_prepare_002 from 'select 1'; execute u_prepare_002;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step11)



    def tearDown(self):
        step12 = 'step12:清理环境 expect:清理环境成功'
        self.log.info(step12)
        clean_sql = "DEALLOCATE PREPARE t_prepare_002; DEALLOCATE PREPARE test_view_002; DEALLOCATE PREPARE c; " \
                    + "DEALLOCATE PREPARE test_index_002; DEALLOCATE PREPARE test_database_002; " \
                    + "DEALLOCATE PREPARE f_prepare_002; DEALLOCATE PREPARE p_prepare_002; " \
                    + "DEALLOCATE PREPARE test_role_002; DEALLOCATE PREPARE test_group_002; " \
                    + "DEALLOCATE PREPARE test_shema_002; DEALLOCATE PREPARE u_prepare_002; " \
                    + "drop view if exists test_view_002; drop table if exists t_prepare_002; " \
                    + "drop database if exists test_database_002; drop function if exists f_prepare_002; " \
                    + "drop procedure if exists p_prepare_002; drop role if exists test_role_002; " \
                    + "drop group if exists test_group_002; drop schema if exists test_shema_002; " \
                    + "drop user if exists u_prepare_002;"
        self.primary_sh.execut_db_sql(clean_sql)
        self.log.info("prepare语句无法跨会话生效，由于自动化用例会跨会话执行这些语句，所以自动化结束时无需清理")
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
